<?php 
/**
 * Mysql
 *
 * Mysql数据库操作类
 *
 * @author   songhuan <iphper@yeah.net>
 * @version   $Id: Mysql.php 1 2010-08-10 14:40:00Z $
 */
class Mysql extends Database implements Adapter
{
    const DISTINCT   = '%DISTINCT%';
	const FIELDS     = '%FIELDS%';
	const TABLE      = '%TABLE%';
	const WHERE      = '%WHERE%';
	const GROUP_BY   = '%GROUP%';
	const HAVING     = '%HAVING%';
	const ORDER_BY   = '%ORDER%';
	const LIMIT      = '%LIMIT%';
    
	protected $_partsInit = array(
		self::DISTINCT => '',  // string  ' DISTINCT' : ''
		self::FIELDS   => '*', // array   array('`t1`.`column1`, `t1`.`column2`, `t2`.`column3`', 'value1', 'value2', 'value3')
		self::TABLE    => '',  // string  '`vo_table1` AS `t1`, `vo_table2`, `table3` AS `t3`'
							   // string  '`vo_table1` AS `t1` LEFT JOIN `table2` LEFT JOIN `vo_table3` AS `t3` ON t1.column1 = t2.column1'
		self::WHERE    => '',  // array   array(' WHERE t1.column1 = ? AND t2.column2 = ? OR column3 = ?', 'value1', 'value2', 'value3')
		self::GROUP_BY => '',  // string   GROUP BY `t1`.`column1`, `t1`.`column2`, `t2`.`column3`
		self::HAVING   => '',  // array   array (' HAVING COUNT(*) > ? AND AVG(SALARY) < ?', '100', '10000')
		self::ORDER_BY => '',  // string   ORDER BY `t1`.`column1` DESC, `t1`.`column2`, `t2`.`column3` DESC
		self::LIMIT    => ''   // array   array('LIMIT ?, ?', 1, 10);
	);
	
    protected $_parts = array();
    
    public function __construct()
    {
    	$this->_initParts();
    }
    
    /**
     * 设置 DISTINCT 值
     * 
     * @param boolean $bool
     * 
     * @return $this
     */
    public function distinct($bool = true)
    {
    	$this->_parts[self::DISTINCT] = $bool ? ' DISTINCT' : '';
    	return $this;
    }
    
    /**
     * 设置字段和字段值
     * 
     * @param array $fields
     * INSERT UPDATE 用Fields
     * array ('column1' => 'value1', 'column2' => 'value2', 
     *  	  'column3' => 'value3', ......)
     * SELECT 用Fields
     * array ('column1', 'column2', 'column3', ......)
     * 仅有一个字段 string
     * @param string $prefix
     * @param string $func
     * 
     * @return $this
     */
    public function fields($fields, $prefix = '', $func = null)
    {
		if ($prefix != '')
			$prefix = '`' . $prefix . '`.';
    	
		$lfunc = $rfunc = '';
		if (null !== $func)
		{
			$lfunc = strtoupper($func) . '(';
			$rfunc = ')';
		}
		
		$column = '';
		$fieldVals = array();
    	if (is_array($fields))
    	{
    		if (isset($fields[0])) 
    			$fieldKeys = $fields;
    		else 
    		{
    			$fieldKeys = array_keys($fields);
    			$fieldVals = array_values($fields);
    		}
			foreach ($fieldKeys as $key => $field)
				$column .= ', ' . $lfunc . $prefix . '`' . $field . '`' . $rfunc;
    	}
    	else 
    		$column = ', ' . $lfunc . $prefix . '`' . (string) $fields . '`' . $rfunc;
    	
		if (is_array($this->_parts[self::FIELDS]))
			$this->_parts[self::FIELDS][0] .= $column;
		else 
			$this->_parts[self::FIELDS] = array($column);
		
		$this->_parts[self::FIELDS][0] = ltrim($this->_parts[self::FIELDS][0], ', ');
		$this->_parts[self::FIELDS] = array_merge($this->_parts[self::FIELDS], $fieldVals);
		
    	return $this;
    }
    
    /**
     * 设置表
     * 
     * @param array|string $tables
     * 有别名的  array ('alias1' => 'table1', 'alias2' => 'table2', ...)
     * 无别名的  array ('table1', 'table2', 'table3', ...)
     * 仅有一个表 string
     * 
     * @return $this
     */
	public function table($tables, $prefix = null)
    {
    	if (null === $prefix)
			$prefix = V::$config['db']['prefix'];
    	
		$_table = '';
		if (is_array($tables))
		{
			foreach ($tables as $alias => $table)
			{
				if (is_numeric($alias))
	    			$_table .= ', `' . $prefix . $table . '`';
	    		else 
	    			$_table .= ', `' . $prefix . $table . '` AS `' . $alias . '`';
			}
		}
		else 
			$_table = ', `' . $prefix . (string) $tables . '`';
		
		$this->_parts[self::TABLE] .= $_table;
		$this->_parts[self::TABLE] = ltrim($this->_parts[self::TABLE], ', ');
		
    	return $this;
    }
    
    /**
     * 设置左连接
     * 
     * @param array|string $tables
     * 有别名的  array ('alias1' => 'table1', 'alias2' => 'table2', ...)
     * 无别名的  array ('table1', 'table2', 'table3', ...)
     * 仅有一个表 string
     * @param string $on
     * 
     * @return $this
     */
    public function join($tables, $on = '', $prefix = null)
    {
    	if (null === $prefix)
			$prefix = V::$config['db']['prefix'];
    	
    	$join = '';
    	if (is_array($tables))
    	{
    	    foreach ($tables as $alias => $table)
	    	{
	    		if (is_numeric($alias))
	    			$join .= ' LEFT JOIN `' . $prefix . $table . '`';
	    		else 
	    			$join .= ' LEFT JOIN `' . $prefix . $table . '` AS `' . $alias . '`';
	    	}	
    	}
    	else 
    		$join = ' LEFT JOIN `' . $prefix . (string) $tables . '`';
    	
    	if ($on != '')
    		$on = ' ON ' . $on;
    	
    	$this->_parts[self::TABLE] .= $join . $on;
    	$this->_parts[self::TABLE] = ltrim($this->_parts[self::TABLE], ' LEFT JOIN ');
    	
    	return $this;
    }
    
    /**
     * 设置 WHERE 条件
     * 
     * @param array $where
     * array ('username = ? AND password = ?', 'iphper', '123456')
     * 
     * @return $this
     */
    public function where(array $where)
    {
    	$this->_parts[self::WHERE] = $where;
    	$this->_parts[self::WHERE][0] = ' WHERE ' . $this->_parts[self::WHERE][0]; 
    	return $this;
    }
    
    /**
     * 设置 GROUP_BY
     * 
     * @param array|string $groups
     * 无别名的  array ('group1', 'group2', 'group3', ...)
     * 仅一个字段 string
     * @param string $prefix
     * 
     * @return $this
     */
    public function group($groups, $prefix = '')
    {
    	if ($prefix != '')
			$prefix = '`' . $prefix . '`.';
    	
		$_group = '';
    	if (is_array($groups))
    		$_group = $prefix . '`' . implode('`, ' . $prefix . '`', $groups) . '`';
		else 
			$_group = $prefix . '`' . (string) $groups . '`';
		
		if (strpos($this->_parts[self::GROUP_BY], ' GROUP BY') === 0)
			$this->_parts[self::GROUP_BY] .= ', ' . $_group;
		else 
    		$this->_parts[self::GROUP_BY] = ' GROUP BY ' . $_group;
    	
    	return $this;
    }
    
    /**
     * 设置 HAVING
     * 
     * @param array $having
     * array ('COUNT(*) > ? AND AVG(SALARY) < ?', '100', '10000')
     * 
     * @return $this
     */
    public function having(array $having)
    {
    	$this->_parts[self::HAVING] = $having;
    	$this->_parts[self::HAVING][0] = ' HAVING ' . $this->_parts[self::HAVING][0]; 
    	return $this;
    }
    
    /**
     * 设置 ORDER_BY
     * 
     * @param array $orders
     * array ('order1' => 'ASC', 'order2' => 'DESC', 'order3', ...)
     * 仅一个字段 string 
     * @param string $prefix
     * 
     * @return $this
     */
    public function order($orders, $prefix = '')
    {
    	if ($prefix != '')
			$prefix = '`' . $prefix . '`.';
    	
		$_order = '';
    	if (is_array($orders))
    	{
    		foreach ($orders as $order => $way)
	    	{
	    		if (is_numeric($order))
	    			$_order .= $prefix . '`' . $way . '`, ';
	    		else 
	    		{
	    			if (strcasecmp('DESC', $way) === 0)
	    				$_order .= $prefix . '`' . $order . '` DESC, ';
	    			else
	    				$_order .= $prefix . '`' . $order . '`, ';
	    		}
	    	}
	    	$_order = rtrim($_order, ', ');
    	}
    	else
    		$_order = $prefix . '`' . (string) $orders . '`';
    	
    	if (strpos($this->_parts[self::ORDER_BY], ' ORDER BY ') === 0)
    		$this->_parts[self::ORDER_BY] .=  ', ' . $_order;
    	else 
    		$this->_parts[self::ORDER_BY] .=  ' ORDER BY ' . $_order;

    	return $this;
    }
    
    /**
     * 设置 LIMIT 值
     * 
     * @param int $offset
     * @param int $count
     * 
     * @return $this
     */
    public function limit($offset, $count = null)
    {
    	if (null === $count)
    	{
    		$count = $offset;
    		$offset = 0;
    	}	
    	
    	$this->_parts[self::LIMIT] = array(' LIMIT ?, ?', (int) $offset, (int) $count);
    	return $this;
    }
    
   	/**
	 * 向表里插入一条记录，返回最后一次插入的ID
	 *
	 * @return int
	 */
    public function insert()
    {
    	$sql = sprintf('INSERT INTO %s (%s) VALUES (%s)', 
						$this->_parts[self::TABLE], 
						array_shift($this->_parts[self::FIELDS]), 
						ltrim(str_repeat(', ?', count($this->_parts[self::FIELDS])), ', '));
						
		$this->query($sql, $this->_parts[self::FIELDS]);
		$this->_initParts();
		return $this->lastInsertId();
    }
    
    /**
	 * 更新表里的一条记录，返回受影响的行数
	 *
	 * @return int
	 */
    public function update()
    {
    	$sql = sprintf('UPDATE %s SET %s = ?%s', 
    					$this->_parts[self::TABLE], 
    					str_replace(',', ' = ?,', array_shift($this->_parts[self::FIELDS])), 
    					array_shift($this->_parts[self::WHERE]));
    					
    	$this->query($sql, array_merge($this->_parts[self::FIELDS], $this->_parts[self::WHERE]));
    	$this->_initParts();
		return $this->rowCount();
    }
    
    /**
	 * 删除表里的一条记录，返回受影响的行数
	 *
	 * @return int
	 */
    public function delete()
    {
    	$sql = sprintf('DELETE FROM %s%s', 
						$this->_parts[self::TABLE],
						array_shift($this->_parts[self::WHERE]));
						
		$this->query($sql, $this->_parts[self::WHERE]);
		$this->_initParts();
		return $this->rowCount();
    }
    
    /**
     * 分析 _parts 获取查询Sql和Params
     * 
     * @return void
     */
    protected function parseFetchArgs()
    {
    	$this->_sql = 'SELECT%DISTINCT% %FIELDS% FROM %TABLE%%WHERE%%GROUP%%HAVING%%ORDER%%LIMIT%';
    	$this->_params = array();
    	foreach ($this->_parts as $part => $value)
    	{
    		if (is_array($value))
    		{
    			$this->_sql = str_replace($part, array_shift($value), $this->_sql);
    			$this->_params = array_merge($this->_params, $value);
    		}
    		else 
    			$this->_sql = str_replace($part, $value, $this->_sql);
    	}
    	
    	$this->_initParts();
    }
    
   	/**
	 * 查询表里的所有记录
	 *
	 * @param string $fetchMode
	 * @return array
	 */
    public function select($fetchMode = null)
    {
    	$this->parseFetchArgs();
    	return $this->fetchAll($this->_sql, $this->_params, $fetchMode);
    }
    
	/**
	 * 查询表里的一条记录
	 * 
	 * @param string $fetchMode
	 * @return array
	 */
	public function find($fetchMode = null) 
	{
		$this->limit(1);
		$this->parseFetchArgs();				
		return $this->fetchOne($this->_sql, $this->_params, $fetchMode);
	}
	
    /**
     * 返回全部结果集中指定列的结果
     *
     * @param int $columnNumber 列号
     * @return array.
     */
    public function getColumn($columnNumber = 0)
    {
		$this->parseFetchArgs();
        return $this->fetchColumn($this->_sql, $this->_params, $columnNumber);
    }
    
    /**
     * 返回一个结果集中指定列的结果
     *
     * @param int $columnNumber 列号
     * @return string|int.
     */
    public function getColumnOne($columnNumber = 0)
    {
		$this->limit(1);
		$this->parseFetchArgs();
        return $this->fetchColumnOne($this->_sql, $this->_params, $columnNumber);
    }
    
	/**
	 * 查询表里的所有记录
	 * 
	 * @return array
	 */
	public function getAssoc() 
	{	
		$this->parseFetchArgs();
		return $this->fetchAssoc($this->_sql, $this->_params);
	}
    
    /**
     * 初始化$_parts数组
     * 
     * @return void
     */
    protected function _initParts()
    {
    	$this->_parts = $this->_partsInit;
    }
    
    /**
     * 返回$_parts数组 - 测试用
     * 
     * @return array
     */
    public function getParts()
    {
    	return $this->_parts;
    }
}
